package dao_Impl;

import interfaces.DALException;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import connector.Connector;
import connector.NonStaticConnector;

import dto_objects.AuthorDTO;
import dto_objects.BookDTO;
import dto_objects.PublisherDTO;
import dto_objects.SubjectDTO;

public class MySQLBookDAO  {

	//NYT
	public List<AuthorDTO> getAuthorsByISBN(int ISBN) throws DALException{
		ResultSet rs = Connector.doQuery("SELECT * FROM Book_Authors NATURAL JOIN Author WHERE ISBN = " + ISBN);
		List<AuthorDTO> tempList = new ArrayList<AuthorDTO>();
		try {
			while(rs.next()){
				tempList.add(new AuthorDTO(rs.getInt(1), rs.getString(3), rs.getString(4)));
			}
			if (!rs.first())
				throw new DALException("Author " + ISBN + " does not excist");
			return tempList;
		}catch (SQLException e) {throw new DALException(e); }
	}
	
	public List<SubjectDTO> getSubjectByISBN(int ISBN) throws DALException{
		ResultSet rs = Connector.doQuery("SELECT * FROM Book_Subjects NATURAL JOIN Subject WHERE ISBN = " + ISBN);
		List<SubjectDTO> tempList = new ArrayList<SubjectDTO>();
		try{
			while(rs.next()){
				tempList.add(new SubjectDTO(rs.getInt(1), rs.getString(3)));
			}
//			if (!rs.first())
//				throw new DALException("Subject " + ISBN + " does not excist");
			return tempList;
		}catch (SQLException e) {
			throw new DALException(e); 
		}
	}
	
	public BookDTO getBookByISBN(int ISBN) throws DALException{
		ResultSet rs = Connector.doQuery("SELECT * FROM Book WHERE ISBN = " + ISBN);
		try{
			if (!rs.first()) 
				throw new DALException("Book " + ISBN + " does not excist");
		
			System.out.println("In try");
			String temp3;
			int temp1, temp2, temp4;
			System.out.println("trying to get temp1");
			temp1 = rs.getInt(1);
			System.out.println(temp1);
			temp2 = rs.getInt(2);
			temp3 = rs.getString(3);
			temp4 = rs.getInt(4);
			System.out.println(temp1 + "  " + temp2 + "  " + temp3 + "  " + temp4);
			return new BookDTO(temp1, getPublisherByID(temp2), getAuthorsByISBN(temp1)
						, getSubjectByISBN(temp1), temp3, temp4); 
		}
		catch (SQLException e) {
			throw new DALException(e); 
		}
	}
	
	//NYT SLUT
	
	public List<BookDTO> getBooksByPublisherID(int publisherID)throws DALException {		
		
		NonStaticConnector conn = null;
		try {
			conn = new NonStaticConnector();
		} catch (InstantiationException | IllegalAccessException
				| ClassNotFoundException | SQLException e1) {
			throw new DALException(e1);
		}
		
		ResultSet rs2 = conn.doQuery("SELECT * FROM Book WHERE publisherID =" + publisherID);
		List<BookDTO> list = new ArrayList<BookDTO>();
		try {
			
			while (rs2.next())			{
				System.out.println("In try");
				String temp3;
				int temp1, temp2, temp4;
				System.out.println("trying to get temp1");
				temp1 = rs2.getInt(1);
				System.out.println(temp1);
				temp2 = rs2.getInt(2);
				temp3 = rs2.getString(3);
				temp4 = rs2.getInt(4);
				System.out.println(temp1 + "  " + temp2 + "  " + temp3 + "  " + temp4);
				System.out.println("publisher: "+getPublisherByID(temp2).getPublisher_name());
				System.out.println("Author: "+getAuthorsByISBN(temp1).get(0).getAuthorFirstName());
				System.out.println("Subject: "+getSubjectByISBN(temp1).get(0).getSubject_name());
				list.add(new BookDTO(temp1, getPublisherByID(temp2), getAuthorsByISBN(temp1)
							, getSubjectByISBN(temp1), temp3, temp4));
			
			}
			if (!rs2.first()) 
				throw new DALException("Publisher with ID " + publisherID + " does not excist");
		}
		catch (SQLException e) { throw new DALException(e); }
		return list;
	}
	
	public void createBook(BookDTO book, int authorID, int subjectID) throws DALException { // skal rettes
		Connector.doUpdate(
				"INSERT INTO Book(ISBN, publisherID, title, pages) VALUES " +
						"(" + book.getISBN() + ", '" + book.getPublisher().getPublisherID() + "', '" + book.getTitle() + "', '" + 
						book.getPages() + "')" 
				);
		Connector.doUpdate(
						"INSERT INTO Book_Authors(authorID, ISBN) VALUES " +
						"(" + authorID + ", '" + book.getISBN() + "')"
				);
		Connector.doUpdate(
						"INSERT INTO Book_Subjects(subjectID, ISBN) VALUES " +
						"(" + subjectID + ", '" + book.getISBN() + "')"
				);
	}

	
	public void updateBook(BookDTO book, int authorID, int subjectID) throws DALException {
		Connector.doUpdate(
				"UPDATE Book SET  ISBN = '" + book.getISBN() + "', publisherID =  '" + book.getPublisher().getPublisherID() + 
				"', title = '" + book.getTitle() + "', pages = '" + book.getPages() + "' WHERE ISBN = " +
				book.getOldISBN() 
				);	
		Connector.doUpdate(
				"UPDATE Book_Authors SET  authorID = '" + authorID + "', ISBN =  '" + book.getISBN() + "' WHERE ISBN = " +
				book.getOldISBN() 
				);	
		Connector.doUpdate(
				"UPDATE Book_Subjects SET  subjectID = '" + subjectID + "', ISBN =  '" + book.getISBN() + "' WHERE ISBN = " +
				book.getOldISBN()
				);				
	}
	
	public void getBookByTitle(String title) throws DALException {
		ResultSet rs = Connector.doQuery("SELECT * FROM Book WHERE title = " + title);
		try {
			if (!rs.first()) throw new DALException("Book " + title + " does not excist");
			getBookByISBN(rs.getInt(1));
		}
		catch (SQLException e) {throw new DALException(e); }
	}
	
	// HER UNDER ARBEJDES DER P��������� BOOK_COPY TABELLEN
	
	
	public BookDTO getBook_CopyByID(int bookID)	throws DALException, SQLException {
		ResultSet rs = Connector.doQuery("SELECT * FROM Book_Copy WHERE bookID = " + bookID);
		if (!rs.first()) throw new DALException("Book Copy " + bookID + " does not excist");
		return new BookDTO(rs.getInt(1), rs.getInt(2), rs.getString(3));

	}
	public int getISBNByBook_CopyID(int bookID)	throws DALException, SQLException {
		ResultSet rs = Connector.doQuery("SELECT ISBN FROM Book_Copy WHERE bookID = " + bookID);
		if (!rs.first()) throw new DALException("Book Copy " + bookID + " does not excist");
		return rs.getInt(1);

	}

	
	public List<BookDTO> getBook_CopyListByISBN(int ISBN) throws DALException {
		List<BookDTO> list = new ArrayList<BookDTO>();
		ResultSet rs = Connector.doQuery("SELECT * FROM Book_Copy WHERE ISBN =" + ISBN);
		try {
			while (rs.next())
			{
				list.add(new BookDTO(rs.getInt(1), rs.getInt(2), rs.getString(3)));
			}
		}
		catch (SQLException e) { throw new DALException(e); }
		return list;
	}

	
	public void createBook_Copy(BookDTO book) throws DALException {
		Connector.doUpdate(
				"INSERT INTO Book_Copy(ISBN, bookID) VALUES " +
						"(" + book.getISBN() + ", '" + book.getBookID() + ", '" + book.getStatus() + "')"
				);				
	}

	
	public void updateBook_Copy(BookDTO book) throws DALException {
		Connector.doUpdate(
				"UPDATE Book_Copy SET  ISBN = '" + book.getISBN() + "', bookID =  '" + book.getBookID() + "', status = '" + book.getStatus() + "' WHERE ISBN = " +
				book.getISBN() + " AND bookID = " + book.getBookID()
				);				
	}
	
	public void deleteBook_Copy(int bookID) throws DALException {
		Connector.doUpdate(
				"UPDATE Book_Copy SET status = 'Udgået' WHERE bookID = " + bookID
				);				
	}

	// HER UNDER ARBEJDES DER P��������� AUTHORS
	
	
	public AuthorDTO getAuthorByID(int authorID) throws DALException {
		ResultSet rs = Connector.doQuery("SELECT * FROM Authors WHERE authorID = " + authorID);
		try {
			if (!rs.first()) throw new DALException("Author " + authorID + " does not excist");
			return new AuthorDTO(rs.getInt(1), rs.getString(2), rs.getString(3));
		}
		catch (SQLException e) {throw new DALException(e); }
	}

	
	public void createAuthor(AuthorDTO author) throws DALException {
		Connector.doUpdate(
				"INSERT INTO Author(authorID, author_firstname) VALUES " +
						"(" + author.getAuthorId() + ", '" + author.getAuthorFirstName() + ", '" + author.getAuthorLastName() + "')"
				);				
	}

	
	public void updateAuthor(AuthorDTO author) throws DALException {
		Connector.doUpdate(
				"UPDATE Author SET  authorID = '" + author.getAuthorId() + "', author_firstname =  '" + author.getAuthorFirstName() 
				+ "', author_lastname =  '" + "' WHERE authorID = " + author.getAuthorId()
				);				
	}
	
	public void deleteAuthor(AuthorDTO author) throws DALException {
		Connector.doUpdate(
				"DELETE FROM Author WHERE  authorID = " + author.getAuthorId() 
				);				
	}

	public AuthorDTO getAuthorByFirstName(String authorFirstName) throws DALException {
		ResultSet rs = Connector.doQuery("SELECT * FROM Author WHERE author_firstname = " + authorFirstName);
		try {
			if (!rs.first()) throw new DALException("Author " + authorFirstName + " does not excist");
			return new AuthorDTO(rs.getInt(1), rs.getString(2), rs.getString(3));
		}
		catch (SQLException e) {throw new DALException(e); }
	}
	
	public AuthorDTO getAuthorByLastName(String authorLastName) throws DALException {
		ResultSet rs = Connector.doQuery("SELECT * FROM Author WHERE author_lastname = " + authorLastName);
		try {
			if (!rs.first()) throw new DALException("Author " + authorLastName + " does not excist");
			return new AuthorDTO(rs.getInt(1), rs.getString(2), rs.getString(3));
		}
		catch (SQLException e) {throw new DALException(e); }
	}
	
	public List<AuthorDTO> getAuthorIdList() throws DALException {
		List<AuthorDTO> list = new ArrayList<AuthorDTO>();
		ResultSet rs = Connector.doQuery("SELECT * FROM Author");
		try
		{
			while (rs.next())
			{
				list.add(new AuthorDTO(rs.getInt(1), rs.getString(2), rs.getString(3)));
			}
		}
		catch (SQLException e) { throw new DALException(e); }
		return list;
	}
	
	
	
	// HER UNDER ARBEJDES DER P��������� PUBLISHER
	
	
	public PublisherDTO getPublisherByID(int publisherID) throws DALException {
		ResultSet rs = Connector.doQuery("SELECT * FROM Publisher WHERE publisherID = " + publisherID);
		try {
			if (!rs.first()) throw new DALException("Publisher " + publisherID + " does not excist");
			PublisherDTO blahDTO = new PublisherDTO(rs.getInt(1), rs.getString(2));
			rs.close();
			return blahDTO;
		}
		catch (SQLException e) {throw new DALException(e); }
	}

	
	public void createPublisher(PublisherDTO publisher) throws DALException {
		Connector.doUpdate(
				"INSERT INTO Publisher(publisherID, publisher_name) VALUES " +
						"(" + publisher.getPublisherID() + ", '" + publisher.getPublisher_name() + "')"
				);						
	}

	
	public void updatePublisher(PublisherDTO publisher) throws DALException {
		Connector.doUpdate(
				"UPDATE Publisher SET  publisherID = '" + publisher.getPublisherID() + "', publisher_name =  '" + publisher.getPublisher_name() 
				+ "' WHERE publisherID = " + publisher.getPublisherID()
				);						
	}

	public void deletePublisher(PublisherDTO publisher) throws DALException {
		Connector.doUpdate(
				"DELETE FROM Publisher WHERE publisherID = " + publisher.getPublisherID() 
				);						
	}

	public PublisherDTO getPublisherByName(String publisher_name) throws DALException {
		ResultSet rs = Connector.doQuery("SELECT * FROM Publisher WHERE publisher_name = " + publisher_name);
		try {
			if (!rs.first()) throw new DALException("Publisher " + publisher_name + " does not excist");
			return new PublisherDTO(rs.getInt(1), rs.getString(2));
		}
		catch (SQLException e) {throw new DALException(e); }
	}
	
	public List<PublisherDTO> getPublisherIdList() throws DALException {
		List<PublisherDTO> list = new ArrayList<PublisherDTO>();
		ResultSet rs = Connector.doQuery("SELECT * FROM Publisher");
		try
		{
			while (rs.next())
			{
				list.add(new PublisherDTO(rs.getInt(1), rs.getString(2)));
			}
		}
		catch (SQLException e) { throw new DALException(e); }
		return list;
	}

	// HER UNDER ARBEJDES DER P��������� SUBJECTS
	
	
	public SubjectDTO getSubjectByID(int subjectID) throws DALException {
		ResultSet rs = Connector.doQuery("SELECT * FROM Subject WHERE subjectID = " + subjectID);
		try {
			if (!rs.first()) throw new DALException("Subject " + subjectID + " does not excist");
			return new SubjectDTO(rs.getInt(1), rs.getString(2));
		}		catch (SQLException e) {throw new DALException(e); }
	}

	
	public void createSubject(SubjectDTO subject) throws DALException {
		Connector.doUpdate(
				"INSERT INTO Subject(subjectID, subject_name) VALUES " +
						"(" + subject.getSubjectID() + ", '" + subject.getSubject_name() + "')"
				);					
	}

	
	public void updateSubject(SubjectDTO subject) throws DALException {
		Connector.doUpdate(
				"UPDATE Subject SET  subjectID = '" + subject.getSubjectID() + "', subject_name =  '" + subject.getSubject_name()
				+ "' WHERE subjectID = " + subject.getSubjectID()
				);						
	}

	public void deleteSubject(SubjectDTO subject) throws DALException {
		Connector.doUpdate(
				"DELETE FROM Subject WHERE subjectID = " + subject.getSubjectID()
				);						
	}

	public SubjectDTO getSubjectByName(String subject_name)
			throws DALException {
		ResultSet rs = Connector.doQuery("SELECT * FROM Subject WHERE subject_name = " + subject_name);
		try {
			if (!rs.first()) throw new DALException("Subject " + subject_name + " does not excist");
			return new SubjectDTO(rs.getInt(1), rs.getString(2));
		}		catch (SQLException e) {throw new DALException(e); }
	}
	
	public List<SubjectDTO> getSubjectIdList() throws DALException {
		List<SubjectDTO> list = new ArrayList<SubjectDTO>();
		ResultSet rs = Connector.doQuery("SELECT * FROM Subject");
		try
		{
			while (rs.next())
			{
				list.add(new SubjectDTO(rs.getInt(1), rs.getString(2)));
			}
		}
		catch (SQLException e) { throw new DALException(e); }
		return list;
	}




}
